<?php

namespace App\Admin\Exporter;

use App\Models\Family;
use App\Models\Hospital;
use App\Models\HospitalTeam;
use App\Models\HospitalUserFamily;
use App\Models\User;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Maatwebsite\Excel\Concerns\FromArray;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\WithTitle;

//sheet名称
use Maatwebsite\Excel\Concerns\WithMultipleSheets;

//sheet
use Maatwebsite\Excel\Concerns\WithStyles;

//造型
use Maatwebsite\Excel\Concerns\WithColumnWidths;

//列宽
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Worksheet\Worksheet;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\WithMapping;

class SignExporter implements WithHeadings, FromCollection, WithStyles
{
    protected $data;

    public function __construct($data)
    {
        $this->data = $data;
    }

    /**
     * 表头
     * @return string[]
     */
    public function headings(): array
    {
        $hospitalName = Hospital::query()->where('id',$this->data['hospital_id'])->value('name');

        return [
            ["{$hospitalName}家庭医生签约服务签约居民基本信息汇总表"],
            ['工作联络人', '', '', '', '', '联系电话'],
            ["区", "中心编码", "机构编码", "机构名称", "签约医生姓名", "签约居民姓名", "签约居民身份证号", "出生日期", "签约日期", "联系电话1", "联系电话2", "联系电话3", "现住址", "派出所", "签约服务包", "导入标识", "邮政编码", "区域行政编码", "镇/街道", "建档日期"]
        ];
    }

    /**
     * 导出
     * @return \Illuminate\Support\Collection|\Tightenco\Collect\Support\Collection
     */
    public function collection()
    {
        $data = $this->data;
        $i=1;
        $hospital_id = $data['hospital_id'];
        $status = $data['status']?:1;
        $where = [];
        switch ($data['type']) {
            case 'bigChild':
                $whereArr[] = "eb_hospital_user_family.type = 1";
                //$whereArr[] = "eb_family.birthday = '".date('Y-m-d',strtotime('-7 year'))."'";
                $signName = "0-6岁儿童基本签约服务包";
                $importType = 3;
                break;
            case 'preg':
                $whereArr[] = "eb_hospital_user_family.type = 2";
                $signName = "孕产妇基本签约服务包";
                $importType = 2;

                break;
        }

        if (isset($data['start'])) {
            $whereArr[] = "eb_hospital_user_family.starttime >= '".$data['start']."'";
        }
        if (isset($data['end'])) {
            $whereArr[] = "eb_hospital_user_family.starttime <= '".$data['end']."'";
        }

        if(!$data['is_admin']) {
            $table = "eb_hospital_user_family,eb_family";
            $whereArr[] = "eb_hospital_user_family.family_id = eb_family.id";
        }else{
            $table = "eb_hospital_user_family,eb_family,eb_import_family";
            $whereArr[] = "eb_hospital_user_family.hospital_id = eb_import_family.hospital_id";
            $whereArr[] = "eb_family.id = eb_import_family.family_id";
            $whereArr[] = "eb_import_family.type = {$importType}";

        }
        $whereArr[] = "eb_hospital_user_family.hospital_id = {$hospital_id}";
        $whereArr[] = "eb_hospital_user_family.`status` = {$status}";
        if($data['type'] =='preg' && $data['preg_status'] == 0){
            $table .= ",eb_family_pregn";
            $whereArr[] = "eb_family_pregn.family_id = eb_family.id";
            $whereArr[] = "eb_family_pregn.status != 3";
        }

        $where = implode(" and ", $whereArr);

        $sql = "SELECT *
FROM (SELECT
eb_family.`name`,
eb_family.birthday,
eb_hospital_user_family.hospital_id,
eb_family.id as family_id,
eb_hospital_user_family.hospital_team_id,
eb_hospital_user_family.user_id,
eb_hospital_user_family.starttime,
eb_hospital_user_family.`status`,
ROW_NUMBER() OVER(
    PARTITION BY eb_family.`name`,
    eb_family.birthday ORDER BY eb_hospital_user_family.starttime ASC
) RN
from $table
where
    {$where}
) M
WHERE M.RN=1";
        //dd($sql);
        $signs = DB::select($sql);


        $list = [];
        foreach($signs as $sign) {
            //dd($sign);

            $hospital = Hospital::query()->where('id',$sign->hospital_id)->first();
            $family = Family::query()->where('id',$sign->family_id)->first();
            $team = HospitalTeam::query()->where('id',$sign->hospital_team_id)->first();
            $user = User::query()->where('id',$sign->user_id)->first();
            $row = [];
            if( isset($family) && $data['is_idcard'] && (!$family->idcard || ($family->idcard_type==4 && strlen($family->idcard)!=18)))
            {
                continue;
            }
            $row[] = $i++;
            $row[] = isset($hospital)?$hospital->code:"";
            $row[] = isset($hospital)?$hospital->code:"";
            $row[] = isset($hospital)?$hospital->name:"";
            $row[] = isset($team)?$team->name:"";
            $row[] = isset($family)?$family->name:"";
            $row[] = isset($family)?$family->idcard:"";
            $row[] = isset($family)?$family->birthday:"";
            $row[] = $sign->starttime;
            $row[] = isset($user)?$user->phone:"";
            $row[] = isset($family)?$family->phone:"";
            $row[] = isset($user)?$user->phone:"";
            $row[] = isset($family)?$family->address:"";
            $row[] = isset($hospital)?$hospital->precinct:"";
            $row[] = $signName;
            $row[] = isset($hospital)?$hospital->code:"";
            $row[] = isset($hospital)?$hospital->post_code:"";
            $row[] = isset($hospital)?$hospital->county:"";
            $row[] = isset($hospital)?$hospital->street:"";
            $date = Carbon::parse($sign->starttime);
            $formattedDate = $date->format('Y-m-d'); // 输出: 2023-10-05
            $row[] = $formattedDate;
            $list[] = $row;
        }
        $this->column = $i;
        return collect($list);
    }

    public function styles(Worksheet $sheet)
    {
        $sheet->mergeCells('A1:T1');

        $sheet->mergeCells('A2:E2');
        $sheet->mergeCells('F2:J2');

        $sheet->getStyle('A1:T1')->getAlignment()->setVertical('center')->setHorizontal('center');//垂直居中
        $sheet->getStyle('A3:T' . ($this->column + 3))->getAlignment()->setVertical('center')->setHorizontal('center');//垂直居中
        $sheet->getRowDimension('1')->setRowHeight(25);//设置指定行高
        $sheet->getRowDimension('3')->setRowHeight(50);//设置指定行高
        $sheet->getDefaultRowDimension()->setRowHeight(22);//设置默认行高


        $sheet->getDefaultColumnDimension()->setWidth(20);
        $sheet->getColumnDimension('D')->setWidth(30);
        $sheet->getColumnDimension('A')->setWidth(10);
        $sheet->getColumnDimension('B')->setWidth(10);
        $sheet->getColumnDimension('C')->setWidth(10);
        $sheet->getColumnDimension('P')->setWidth(10);
        $sheet->getColumnDimension('Q')->setWidth(10);
        $sheet->getColumnDimension('C')->setWidth(10);
        $sheet->getColumnDimension('E')->setWidth(15);
        $sheet->getColumnDimension('F')->setWidth(15);
        $sheet->getColumnDimension('H')->setWidth(15);
        $sheet->getColumnDimension('I')->setWidth(15);
        $sheet->getColumnDimension('O')->setWidth(25);
        $sheet->getColumnDimension('T')->setWidth(15);

        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_MEDIUM,
                    'color' => ['argb' => '00000000'],
                ],
            ],
        ];

        $sheet->getStyle('A3:T3')->applyFromArray($styleArray);

        $styleArray = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => \PhpOffice\PhpSpreadsheet\Style\Border::BORDER_THIN //细边框
                ]
            ]
        ];
        $sheet->getStyle('A4:T' . ($this->column + 2))->applyFromArray($styleArray);
    }

}
